package itsm.isperp.module.controller.individuation;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.data.domain.QueryFilter;
import itsm.isperp.framework.web.controller.BaseController;
import itsm.isperp.framework.web.request.JqGridFilter;
import itsm.isperp.framework.web.request.JqGridFilterRule;
import itsm.isperp.framework.web.request.JqGridFilterRuleOp;
import itsm.isperp.framework.web.request.JqGridRequest;
import itsm.isperp.framework.web.response.ResultMessage;
import itsm.isperp.module.entity.individuation.IddCategory;
import itsm.isperp.module.entity.individuation.IddCategoryProperty;
import itsm.isperp.module.service.individuation.IddCategoryPropertyService;
import itsm.isperp.module.service.individuation.IddCategoryService;

import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

/**
 * 控制器
 * 
 * @author lizx
 * @date 2016-07-21 18:05
 */
@Controller
@RequestMapping("individuation/category/property")
public class IddCategoryPropertyController extends BaseController<IddCategoryProperty> {

	@Autowired
	protected IddCategoryPropertyService IddCategoryPropertyService;
	@Autowired
	protected IddCategoryService iddCategoryService;

	@Override
	public IddCategoryPropertyService getService() {
		return this.IddCategoryPropertyService;
	}

	@Override
	public String getPageTitle() {
		return "扩展字段";
	}

	/**
	 * 编辑界面
	 * 
	 * @param id
	 *            唯一标识
	 * @return 编辑视图
	 */
	@Override
	@RequestMapping("edit")
	public ModelAndView edit(String id, HttpServletRequest req) {

		ModelAndView mv = new ModelAndView(this.getModulePath() + "/edit");
		IddCategoryProperty model = null;
		String categoryId="";
		String columnId="";
		if (StringUtils.isEmpty(id)) {
		  categoryId = req.getParameter("categoryId");
			if (StringUtils.isEmpty(categoryId)) {
				return null;
			}
			model = new IddCategoryProperty();
			model.setCategoryId(categoryId);
			QueryFilter filter = new JqGridFilter();
	    filter.addRule("categoryId", JqGridFilterRuleOp.eq, categoryId);
			Long v=IddCategoryPropertyService.findAllCount(filter);
			columnId=categoryId+"_"+v+"p";
		} else {
			model = IddCategoryPropertyService.findOne(id);
			categoryId=model.getCategoryId();
			columnId=model.getId();
		}

		mv.addObject("pageTitle", this.getPageTitle());
		model.setId(columnId);
		mv.addObject("model", model);
		mv.addObject("categoryId", categoryId);
		return mv;
	}

	@RequestMapping("getPropertyCountByCategoryPath")
	@ResponseBody
	public long getPropertyCountByCategoryPath(String categoryPath) {

		return this.IddCategoryPropertyService.getPropertyCountByCategoryPath(categoryPath);
	}

	@RequestMapping("createDbTable")
	@ResponseBody
	public ResultMessage createDbTable(String dbName) {
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		// 根据分类ID 获取需要重建的columns
		String ifExists="SHOW TABLES LIKE 'idd_db_"+dbName+"'";
		List l=jdbcTemplate.queryForList(ifExists);
		if(l.size()>0)
		{
		  String selectCount="select count(1) c from idd_db_"+dbName+"";
		  Integer c=jdbcTemplate.queryForObject(selectCount, Integer.class);
		  if(c==0)
		  {
		    jdbcTemplate.update("drop table "+"idd_db_"+dbName);
		  }else
		  {
		    return ResultMessage.newInstance(false, "已存在该表");
		  }
		}
		  
		String sql0 = "select id,data_type from idd_category_property where category_id='" + dbName + "' order by order_num";
		StringBuilder tcolumns = new StringBuilder();
		String columns = "";
		int textAreaSize = 0;
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql0);
		for (Map<String, Object> li : list) {
			String columnName = li.get("id").toString();
			String dataType = li.get("data_type").toString();
			if (dataType.equals("textarea"))
				textAreaSize++;
			String columnType = "";
			if (dataType.equals("textarea") && textAreaSize < 6) {
				columnType = "VARCHAR(2000)";
			} else if (dataType.equals("textarea") && textAreaSize > 5) {
				columnType = "TEXT";
			} else {
				columnType = "VARCHAR(200)";
			}

			tcolumns.append(columnName + " " + columnType + ",");

		}
		columns = tcolumns.toString();
		String sql = "CREATE TABLE idd_db_" + dbName + "(relation_id VARCHAR(80) NOT NULL," + columns + " PRIMARY KEY (relation_id));";

		try {
			jdbcTemplate.execute(sql);
		} catch (Exception ex) {
			return ResultMessage.newInstance(false, "已存在该表");
		}

		return ResultMessage.newInstance(true);
	}

	/**
	 * 根据配置分类Id查询所包含的所有自定义属性
	 * 
	 * @param categoryId
	 *            配置分类Id
	 * @return json数据结果
	 */
	@RequestMapping("listByCategory")
	@ResponseBody
	public List<Map<String, Object>> listByCategory(String categoryId, JqGridRequest req) {

		if (StringUtils.isEmpty(categoryId)) {
			JqGridFilter filter = req.getFilter();
			if (filter == null) {
				//return this.getService().findAllProperty(categoryId);
				return null;
			}
			List<JqGridFilterRule> list = filter.getRules();
			for (JqGridFilterRule rule : list) {
				if ("categoryId".equals(rule.getField())) {
					categoryId = rule.getData();
					break;
				}
			}
		}
		if(StringUtils.isEmpty(categoryId))
			return null;
		return this.getService().findAllProperty(categoryId);

	}

	@RequestMapping("getCategoryInfo")
	@ResponseBody
	public Map<String, Object> getCategoryInfo(String categoryId, JqGridRequest req) {
		return this.getService().getCategoryInfo(categoryId);
	}

	@RequestMapping("getDataByTableAndRelationId")
	@ResponseBody
	public List<Map<String, Object>> getDataByTableAndRelationId(String tableName, String relationId, JqGridRequest req) {
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		// 根据分类ID 获取需要重建的columns
		String sql = "select * from " + tableName + " where relation_id='" + relationId + "'";
		return jdbcTemplate.queryForList(sql);
	}

	@RequestMapping("saveDataByTableAndRelationId")
	@ResponseBody
	public ResultMessage saveDataByTableAndRelationId(HttpServletRequest request, JqGridRequest req) {
		Map<String, String[]> mp = request.getParameterMap();
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		Set<String> set = mp.keySet();
		String tableName = request.getParameter("tableName");
		String relationId = request.getParameter("relationId");
		String columns = "";
		String values = "";
		for (String k : set) {
			if (k.indexOf("gc_") > -1) {
				String tk = k.replace("gc_", "");
				String tv = "";
				String[] proValue = mp.get(k);
				for (String s1 : proValue) {
					tv += s1 + ",";
				}
				tv = tv.substring(0, tv.length() - 1);
				columns += tk + ",";
				values += "'" + tv + "',";
			}
		}
		columns = columns.substring(0, columns.length() - 1);
		values = values.substring(0, values.length() - 1);
		String insertSql = "insert into " + tableName + "(id,relation_id," + columns + ")" + " values(uuid(),'" + relationId + "'," + values + ")";
		try {
			jdbcTemplate.execute(insertSql);
			return ResultMessage.newInstance(true);
		} catch (Exception ex) {
			return ResultMessage.newInstance(false);
		}
	}

	@RequestMapping("updateDataByTableAndId")
	@ResponseBody
	public ResultMessage updateDataByTableAndId(HttpServletRequest request, JqGridRequest req) {
		Map<String, String[]> mp = request.getParameterMap();
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		Set<String> set = mp.keySet();
		String tableName = request.getParameter("tableName");
		String id = request.getParameter("id");
		String columns = "";
		String values = "";
		String updateSql = "";
		for (String k : set) {
			if (k.indexOf("gc_") > -1) {
				String tk = k.replace("gc_", "");
				String tv = "";
				String[] proValue = mp.get(k);
				for (String s1 : proValue) {
					tv += s1 + ",";
				}
				tv = tv.substring(0, tv.length() - 1);
				updateSql += tk + "='" + tv + "',";
			}
		}
		updateSql = updateSql.substring(0, updateSql.length() - 1);
		updateSql = "update " + tableName + " set " + updateSql + " where id='" + id+"'";
		try {
			if (StringUtils.isNotEmpty(updateSql))
				jdbcTemplate.execute(updateSql);
			return ResultMessage.newInstance(true);
		} catch (Exception ex) {
			return ResultMessage.newInstance(false);
		}
	}

	@RequestMapping("deleteByTableAndRelationId")
	@ResponseBody
	public ResultMessage deleteByTableAndRelationId(HttpServletRequest request, JqGridRequest req) {
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		String tableName = request.getParameter("tableName");
		String id = request.getParameter("id");
		String deleteSql = "delete from " + tableName + " where id='" + id + "'";
		jdbcTemplate.execute(deleteSql);
		return ResultMessage.newInstance(true);

	}
	
	@RequestMapping("save")
	@ResponseBody
	@Override
	public ResultMessage save(IddCategoryProperty model, HttpServletRequest req) {
		
		  String categoryId=model.getCategoryId();
		  //查询是否生成了表
		  String columnName=model.getId();
		  //String columnDataType=model.getDataType();//暂时不处理类型，保存的都是text类型
		  try{
			  JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
			  String sql0=" SELECT * FROM idd_category_property_setting WHERE  category_id='"+categoryId+"' AND db_name IS NOT NULL";
			  List<Map<String,Object>> list=jdbcTemplate.queryForList(sql0);
			  if(list.size()>0)
			  {
				  
				  String tableName=list.get(0).get("db_name").toString();
				  String sql="ALTER TABLE "+tableName+" ADD COLUMN "+columnName+" TEXT NULL";
				  jdbcTemplate.execute(sql);
			  }
		  }catch(Exception ex)
		  {
		  }
		  this.getService().saveOrUpdate(model);
	      return ResultMessage.newInstance(true);
	}
	@RequestMapping("delete")
	@ResponseBody
	@Override
	public ResultMessage delete(String id, HttpServletRequest req) {
		  IddCategoryProperty model=this.getService().findOne(id);
		  String categoryId=model.getCategoryId();
		  //查询是否生成了表
		  String columnName=model.getId();
		  //String columnDataType=model.getDataType();//暂时不处理类型，保存的都是text类型
		  try{
			  JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
			  String sql0=" SELECT * FROM idd_category_property_setting WHERE  category_id='"+categoryId+"' AND db_name IS NOT NULL";
			  List<Map<String,Object>> list=jdbcTemplate.queryForList(sql0);
			  if(list.size()>0)
			  {
				  this.getService().delete(id);
				  String tableName=list.get(0).get("db_name").toString();
				  String sql="ALTER TABLE "+tableName+" drop COLUMN "+columnName;
				  jdbcTemplate.execute(sql);
			  }
		  }catch(Exception ex)
		  {
		  }
	      return ResultMessage.newInstance(true);
	}
}
